package com.nova.edc.service.system.impl;

import java.util.List;
import java.util.Map;

import com.nova.edc.dao.system.CustomerInfoDao;
import com.nova.edc.model.system.CustomerInfo;
import com.nova.edc.model.system.Login;
import com.nova.edc.model.system.ModelConstant;
import com.nova.edc.service.system.CustomerInfoService;
import com.nova.edc.service.system.CustomerMapService;
import com.nova.frame.dao.criterion.Restrictions;
import com.nova.frame.utils.JdbcUtils;
import com.nova.module.sql.SqlBuffer;
import org.springframework.beans.factory.annotation.Autowired;

public class CustomerMapServiceImpl implements CustomerMapService {

    @Autowired
    private CustomerInfoDao novaCustomerInfoDao;

    @Override
    public List<Map<String, Object>> findFirstCustomerMap(String loginName) {
        SqlBuffer sqlBuffer = new SqlBuffer("SELECT " +
                "  detail.*, " +
                "  t_map_area.Zoom " +
                "FROM (SELECT " +
                "        Province position, " +
                "        count(1) num " +
                "      FROM t_customerinfo " +
                "      WHERE AddBy = ? AND Scrapped " +
                "                                = 0 AND Province IS NOT NULL AND Lng IS NOT NULL " +
                "      GROUP BY Province) detail " +
                "  LEFT JOIN t_map_area " +
                "    ON detail.position = t_map_area.NAME " +
                "ORDER BY num DESC ");
        List<Map<String, Object>> list = JdbcUtils.queryMap(sqlBuffer.getSql(),
                loginName);
        return list;
    }

    @Override
    public List<Map<String, Object>> findFirstCustomerMapGroup(Integer groupCode) {
        SqlBuffer sqlBuffer = new SqlBuffer("SELECT " +
                "  detail.*, " +
                "  t_map_area.Zoom " +
                "FROM (SELECT " +
                "        Province position, " +
                "        count(1) num " +
                "      FROM t_customerinfo " +
                "      WHERE GroupCode = ? AND Scrapped " +
                "                                = 0 AND Province IS NOT NULL AND Lng IS NOT NULL " +
                "      GROUP BY Province) detail " +
                "  LEFT JOIN t_map_area " +
                "    ON detail.position = t_map_area.NAME " +
                "ORDER BY num DESC ");
        List<Map<String, Object>> list = JdbcUtils.queryMap(sqlBuffer.getSql(),
                groupCode);
        return list;
    }

    @Override
    public List<Map<String, Object>> findLastCustomerMap(String loginName) {
        SqlBuffer sqlBuffer = new SqlBuffer(
                "SELECT t_customerinfo.CompanyCode companycode, t_customerinfo.CustomerName customername,"
                        + " t_customerinfo.lng lng, t_customerinfo.lat lat, t_customerinfo.Province province,t_customerinfo.City city,t_customerinfo.District district"
                        + " FROM t_customerinfo "
                        + " WHERE Scrapped = 0 AND AddBy = ? AND t_customerinfo.Lng IS NOT NULL");
        List<Map<String, Object>> list = JdbcUtils.queryMap(sqlBuffer.getSql(),
                loginName);
        return list;
    }

    @Override
    public CustomerInfo getCustomerInfoByProvince(String province) {
        return novaCustomerInfoDao.query().add(Restrictions.eq("t_customerinfo.Province", province)).get();
    }

    @Override
    public List<Map<String, Object>> findSecondCustomerMap(String province, Login login) {
        CustomerInfo customerInfo = getCustomerInfoByProvince(province);
        if (login.getRoletype() != null && login.getRoletype() == ModelConstant.ROLE_GROUP) {
            if (customerInfo != null && (customerInfo.getProvince().equals(customerInfo.getCity()))) {
                SqlBuffer sqlBuffer = new SqlBuffer(
                        "SELECT District position, count(1) num FROM t_customerinfo "
                                + "WHERE GroupCode = ? AND Province = ? AND Scrapped = 0 AND Province IS NOT NULL AND Lng IS NOT NULL"
                                + " GROUP BY District ORDER BY num DESC");
                List<Map<String, Object>> list = JdbcUtils.queryMap(sqlBuffer.getSql(),
                        login.getGroupcode(), province);
                return list;
            } else {
                SqlBuffer sqlBuffer = new SqlBuffer(
                        "SELECT City position, count(1) num FROM t_customerinfo "
                                + "WHERE GroupCode = ? AND Province = ? AND Scrapped = 0 AND City IS NOT NULL AND Lng IS NOT NULL"
                                + " GROUP BY City ORDER BY num DESC");
                List<Map<String, Object>> list = JdbcUtils.queryMap(sqlBuffer.getSql(),
                        login.getGroupcode(), province);
                return list;
            }
        } else {
            if (customerInfo != null && (customerInfo.getProvince().equals(customerInfo.getCity()))) {
                SqlBuffer sqlBuffer = new SqlBuffer(
                        "SELECT District position, count(1) num FROM t_customerinfo "
                                + "WHERE AddBy = ? AND Province = ? AND Scrapped = 0 AND Province IS NOT NULL AND Lng IS NOT NULL"
                                + " GROUP BY District ORDER BY num DESC");
                List<Map<String, Object>> list = JdbcUtils.queryMap(sqlBuffer.getSql(),
                        login.getUsername(), province);
                return list;
            } else {
                SqlBuffer sqlBuffer = new SqlBuffer(
                        "SELECT City position, count(1) num FROM t_customerinfo "
                                + "WHERE AddBy = ? AND Province = ? AND Scrapped = 0 AND City IS NOT NULL AND Lng IS NOT NULL"
                                + " GROUP BY City ORDER BY num DESC");
                List<Map<String, Object>> list = JdbcUtils.queryMap(sqlBuffer.getSql(),
                        login.getUsername(), province);
                return list;
            }
        }
    }
}
